#include "head.h"
#include <sys/types.h>
#include <sys/socket.h>
//#include <sys/epoll.h>
#include <sqlite3.h>
#include <stdio.h>
#include <sys/types.h>
#include <stdlib.h>
#include <netinet/in.h>
#include <netinet/ip.h>
#include <arpa/inet.h>
#include <unistd.h>
#include <string.h>
#include "server.h"

#define ERRLOG(errmsg)                                          \
    do                                                          \
    {                                                           \
        perror(errmsg);                                         \
        printf("%s--%s--(%d)\n", __FILE__, __func__, __LINE__); \
        exit(-1);                                               \
    } while (0)

sqlite3 *proc_init()
{
    sqlite3 *db = NULL;
    char *errmsg = NULL;
    int ret;
    if (SQLITE_OK != (ret = sqlite3_open("hqyj.db", &db)))
    {
        printf("%s(%d) : errcode[%d]  errstr:[%s]\n", __FILE__, __LINE__, ret, sqlite3_errmsg(db));
        exit(-1);
    }
    printf("数据库成功打开\n");
    char psql[256] = {"CREATE TABLE IF NOT EXISTS staff(id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, sex CHAR, salary INT, role INT)"};
    if (SQLITE_OK != (ret = sqlite3_exec(db, psql, NULL, NULL, &errmsg)))
    {
        printf("%s(%d) : errcode[%d]  errstr:[%s]\n", __FILE__, __LINE__, ret, sqlite3_errmsg(db));
        exit(-1);
    }
    char psql2[256] = {"CREATE TABLE IF NOT EXISTS usr_msg(id INT PRIMARY KEY, passwd TEXT, role INT, flag INT default 0)"};
    if (SQLITE_OK != (ret = sqlite3_exec(db, psql2, NULL, NULL, &errmsg)))
    {
        printf("%s(%d) : errcode[%d]  errstr:[%s]\n", __FILE__, __LINE__, ret, sqlite3_errmsg(db));
        exit(-1);
    }
    printf("建表成功\n");
    return db;
}
void login_func(sqlite3 *db, msg_t *msg, int acceptfd)
{
    int ret;
    int flag = -1;
    char *errmsg = NULL;
    char **result = NULL;
    int nrow = 0;
    int ncolumn = 0;
    char psql[256] = {0};
    sprintf(psql, "SELECT flag FROM usr_msg WHERE id=%d and passwd='%s'", msg->id, msg->text);
    //执行sql语句
    if (SQLITE_OK != (sqlite3_get_table(db, psql, &result, &nrow, &ncolumn, &errmsg)))
    {
        printf("error:%s\n", errmsg);
    }
    printf("ncolumn=%d\n", ncolumn);
    if (nrow > 0)
    {
        flag = atoi(result[ncolumn]);

        if (flag == 0)
        {
            printf("登录成功\n");
            strcpy(msg->text, "OK");
            sprintf(psql, "UPDATE usr_msg SET flag=1 WHERE id=%d ", msg->id);
            if (SQLITE_OK != (ret = sqlite3_exec(db, psql, NULL, NULL, &errmsg)))
            {
                printf("%s(%d) : errcode[%d]  errstr:[%s]\n", __FILE__, __LINE__, ret, sqlite3_errmsg(db));
                exit(-1);
            }
        }
        else if (flag == 1)
        {
            printf("重复登录\n");
            strcpy(msg->text, "repeat login");
        }
    }
    if (-1 == write(acceptfd, msg, sizeof(msg_t)))
    {
        ERRLOG("write error\n");
    }

    sqlite3_free_table(result);
    sqlite3_free(errmsg);
    return;
}
void register_func(sqlite3 *db, msg_t *msg, int acceptfd)
{
    int ret;
    char psql[256] = {0};
    char *errmsg = NULL;
    sprintf(psql, "INSERT INTO usr_msg(id, passwd, role) values(%d, '%s', %d)", msg->id, msg->text, msg->role);

    //执行sql语句
    if (SQLITE_OK != (ret = sqlite3_exec(db, psql, NULL, NULL, &errmsg)))
    {
        printf("%s(%d) : errcode[%d]  errstr:[%s]\n", __FILE__, __LINE__, ret, sqlite3_errmsg(db));
        exit(-1);
    }
    printf("注册成功\n");
    strcpy(msg->text, "OK");
    if (-1 == write(acceptfd, msg, sizeof(msg_t)))
    {
        ERRLOG("write error\n");
    }
}

void add_func(sqlite3 *db, msg_t *msg, int acceptfd)
{
    int ret;
    char psql[256] = {0};
    char *errmsg = NULL;
    printf("add_func INSERT INTO staff values(%d,'%s','%c', %d, %d)", msg->id, msg->name, msg->sex, msg->salary, msg->flag);
    sprintf(psql, "INSERT INTO staff(name,sex,salary,role) values('%s','%c', %d, %d)", msg->name, msg->sex, msg->salary, msg->flag);

    //执行sql语句
    if (SQLITE_OK != (ret = sqlite3_exec(db, psql, NULL, NULL, &errmsg)))
    {
        printf("%s(%d) : errcode[%d]  errstr:[%s]\n", __FILE__, __LINE__, ret, sqlite3_errmsg(db));
        exit(-1);
    }
    printf("插入成功...\n");
    strcpy(msg->text, "OK");
    if (-1 == write(acceptfd, msg, sizeof(msg_t)))
    {
        ERRLOG("write error\n");
    }
}
void delete_func(sqlite3 *db, msg_t *msg, int acceptfd)
{
    int ret;
    char psql[256] = {0};
    char *errmsg = NULL;
    sprintf(psql, "DELETE FROM staff WHERE id=%d", msg->id);

    //执行sql语句
    if (SQLITE_OK != (ret = sqlite3_exec(db, psql, NULL, NULL, &errmsg)))
    {
        printf("%s(%d) : errcode[%d]  errstr:[%s]\n", __FILE__, __LINE__, ret, sqlite3_errmsg(db));
        exit(-1);
    }
    printf("删除成功...\n");
    strcpy(msg->text, "OK");
    if (-1 == write(acceptfd, msg, sizeof(msg_t)))
    {
        ERRLOG("write error\n");
    }
}
void modify_func(sqlite3 *db, msg_t *msg, int acceptfd)
{
    int ret;
    char psql[256] = {0};
    char *errmsg = NULL;
    sprintf(psql, "UPDATE staff SET name='%s',sex='%c',salary=%d,role=%d WHERE id=%d", msg->name, msg->sex, msg->salary, msg->flag, msg->id);

    //执行sql语句
    if (SQLITE_OK != (ret = sqlite3_exec(db, psql, NULL, NULL, &errmsg)))
    {
        printf("%s(%d) : errcode[%d]  errstr:[%s]\n", __FILE__, __LINE__, ret, sqlite3_errmsg(db));
        exit(-1);
    }
    printf("修改成功...\n");
    strcpy(msg->text, "OK");
    if (-1 == write(acceptfd, msg, sizeof(msg_t)))
    {
        ERRLOG("write error\n");
    }
}
void quit_func(sqlite3 *db, msg_t *msg, int acceptfd)
{
    int ret;
    char *errmsg = NULL;
    char **result = NULL;
    char psql[256] = {0};
    sprintf(psql, "UPDATE usr_msg SET flag=0 WHERE id=%d ", msg->id);
    if (SQLITE_OK != (ret = sqlite3_exec(db, psql, NULL, NULL, &errmsg)))
    {
        printf("%s(%d) : errcode[%d]  errstr:[%s]\n", __FILE__, __LINE__, ret, sqlite3_errmsg(db));
        exit(-1);
    }
    strcpy(msg->text, "OK");
    if (-1 == write(acceptfd, msg, sizeof(msg_t)))
    {
        ERRLOG("write error\n");
    }
}

void select_func(sqlite3 *db, msg_t *msg, int acceptfd)
{
    int ret, i;
    char psql[256] = {0};
    char *errmsg = NULL;
    char **result = NULL;
    int nrow = 0;
    int ncolumn = 0;

    if (strcmp(msg->text, "all") == 0)
    {
        printf("select_func all\n");
        strcpy(psql, "SELECT * FROM staff");
    }
    else
    {
        printf("select_func id=%d\n", msg->id);
        sprintf(psql, "SELECT * FROM staff WHERE id=%d", msg->id);
    }
    //执行sql语句
    if (SQLITE_OK != (sqlite3_get_table(db, psql, &result, &nrow, &ncolumn, &errmsg)))
    {
        printf("error:%s\n", errmsg);
    }
    //函数 sqlite3_get_table 执行成功后，会讲结果集的地址保存在
    //我们定义的result中，我们需要自己处理result
    if (nrow > 0)
    {
        printf("查询成功...%d, %d\n", nrow, ncolumn);
        strcpy(msg->text, "OK");
        if (-1 == write(acceptfd, msg, sizeof(msg_t)))
        {
            ERRLOG("write error\n");
        }

        int index = ncolumn;

        for (i = 0; i < nrow; i++)
        {
            for (int j = 0; j < ncolumn; j++)
            {
                strcpy(msg->text, result[index++]);
                if (-1 == write(acceptfd, msg, sizeof(msg_t)))
                {
                    ERRLOG("write error\n");
                }
            }
        }
        strcpy(msg->text, "**over**");
        if (-1 == write(acceptfd, msg, sizeof(msg_t)))
        {
            ERRLOG("write error\n");
        }
    }
    sqlite3_free_table(result);
    sqlite3_free(errmsg);
    return;
}

int main(int argc, const char *argv[])
{
    if (argc != 3)
    {
        printf("usage %s <ip> <port> \n", argv[0]);
        exit(-1);
    }

    //创建套接字
    int skfd;
    if (-1 == (skfd = socket(AF_INET, SOCK_STREAM, 0)))
    {
        ERRLOG("socket error\n");
    }
    //填充服务器网络信息结构体
    struct sockaddr_in serveraddr;
    serveraddr.sin_family = AF_INET;
    serveraddr.sin_port = htons(atoi(argv[2]));
    serveraddr.sin_addr.s_addr = inet_addr(argv[1]);
    socklen_t addrlen = sizeof(serveraddr);

    //绑定服务器网络信息结构体
    if (-1 == bind(skfd, (struct sockaddr *)&serveraddr, addrlen))
    {
        ERRLOG("bind error\n");
    }
    //设置被动监听
    if (-1 == listen(skfd, 5))
    {
        ERRLOG("listen error\n");
    }
    //定义用来保存客户端信息的网络信息结构体
    struct sockaddr_in clientaddr;
    socklen_t client_addrlen = sizeof(clientaddr);

    // epoll创建一张表
    int epfd, ret, i, ret2;
    char buf[256] = {0};
    char psql[256] = {0};
    char *errmsg = NULL;
    // struct epoll_event event;
    // struct epoll_event revents[50];
    // if (-1 == (epfd = epoll_create(10)))
    // {
    //     ERRLOG("epoll create error\n");
    // }
    //初始化数据库
    sqlite3 *db = proc_init();
    int acceptfd;
    while (1)
    {
        //等待客户端连接
        printf("等待客户端连接\n");
        acceptfd = accept(skfd, (struct sockaddr *)&clientaddr, &client_addrlen);
        if (-1 == acceptfd)
        {
            ERRLOG("accept error\n");
        }
        printf("连接成功\n");
        // event.events = EPOLLIN;
        // event.data.fd = acceptfd;
        msg_t msg;

        // if (epoll_ctl(epfd, EPOLL_CTL_ADD, event.data.fd, &event))
        // {
        //     ERRLOG("epoll ctl error\n");
        // }

        // if (-1 == (ret = epoll_wait(epfd, revents, 50, -1)))
        // {
        //     ERRLOG("epoll wait error\n");
        // }
        // for (i = 0; i < ret; i++)
        // {
        while (1)
        {
            ret2 = read(acceptfd, &msg, sizeof(msg_t));
            if (-1 == ret2)
            {
                ERRLOG("read error\n");
            }
            else if (ret2 == 0)
            {
                quit_func(db, &msg, acceptfd);
                printf("客户端成功退出~！\n");
            }

            /*
            if (revents[i].events & EPOLLIN)
            {
                if (-1 == (ret2 = read(revents[i].data.fd, &msg, sizeof(msg_t))))
                {
                    ERRLOG("read error\n");
                }
                else if (ret2 == 0)
                {
                    printf("客户端%d断开连接\n", msg.id);
                    sprintf(psql, "UPDATE usr_msg SET flag=0 WHERE id=%d ", msg.id);
                    printf("pssql=%s\n", psql);
                    if (SQLITE_OK != (ret = sqlite3_exec(db, psql, NULL, NULL, &errmsg)))
                    {
                        printf("%s(%d) : errcode[%d]  errstr:[%s]\n", __FILE__, __LINE__, ret, sqlite3_errmsg(db));
                        exit(-1);
                    }
                }
                else
                {
                    printf("code=%d,id=%d,text=%s,role=%d\n", msg.code, msg.id, msg.text, msg.role);
                   if (-1 == (ret2 = read(acceptfd, &msg, sizeof(msg_t))))
                {
                    ERRLOG("read error\n");
                }
                else if (ret2 == 0)
                {
                    printf("客户端%d断开连接\n", msg.id);
                    sprintf(psql, "UPDATE usr_msg SET flag=0 WHERE id=%d ", msg.id);
                    printf("pssql=%s\n", psql);
                    if (SQLITE_OK != (ret = sqlite3_exec(db, psql, NULL, NULL, &errmsg)))
                    {
                        printf("%s(%d) : errcode[%d]  errstr:[%s]\n", __FILE__, __LINE__, ret, sqlite3_errmsg(db));
                        exit(-1);
                    }
                    printf("id=%d,flag=%d\n", msg.id,msg.flag);
                }else{
                    printf("code=%d,id=%d,text=%s,role=%d\n", msg.code, msg.id, msg.text, msg.role);

                    */
            switch (msg.code)
            {
            case 'L':
                login_func(db, &msg, acceptfd);
                printf("******************\n");
                break;
            case 'R':
                register_func(db, &msg, acceptfd);
                break;
            case 'A':
                add_func(db, &msg, acceptfd);
                break;
            case 'D':
                delete_func(db, &msg, acceptfd);
                break;
            case 'M':
                modify_func(db, &msg, acceptfd);
                break;
            case 'S':
                select_func(db, &msg, acceptfd);
                break;
            case 'Q':
                quit_func(db, &msg, acceptfd);
                break;
            }
        }
        // }
        // }
        // }
    }

    close(skfd);
    close(acceptfd);
    return 0;
}